package org.lq.shopping.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.lq.shopping.dao.ShopGoodsDao;
import org.lq.shopping.entity.ShopGoods;
import org.lq.shopping.util.DruidUtil;


public class ShopGoodsDaoImpl implements ShopGoodsDao {
	Logger log = Logger.getLogger(ShopGoodsDaoImpl.class);
	QueryRunner qr=new QueryRunner(DruidUtil.getDataSource());

	@Override
	public int add(ShopGoods t) {
		log.info("这是一个添加商品的操作");
		int num=0;
		String sql="insert into shop_goods(goods_id, goods_name, goods_price, goodstype_id, goods_img, goods_desc, goods_exchange, goods_integral, goods_detail, goods_introduction, goods_brand) values (GOODS_SEQ.nextval,?,?,?,?,?,?,?,?,?,?)";
		log.debug("传入值:"+t.toString());
		log.debug("SQL:"+sql);
		try {
			num=qr.execute(sql, t.getGoods_name(),t.getGoods_price(),t.getGoodstype_id(),t.getGoods_img(),t.getGoods_desc(),t.getGoods_exchange(),t.getGoods_integral(),t.getGoods_detail(),t.getGoods_introduction(),t.getGoods_brand());
		} catch (SQLException e) {
			log.error("这是一个添加商品的错误");
		}
		return num;
	}

	@Override
	public int delete(int id) {
		log.info("这是一个删除商品的操作");
		int num=0;
		String sql="delete shop_goods where goods_id = ?";
		log.debug("传入值:"+id);
		log.debug("SQL:"+sql);
		try {
			num=qr.execute(sql, id);
		} catch (SQLException e) {
			log.error("这是一个删除商品的错误");
		}
		return num;
	}

	@Override
	public int update(ShopGoods t) {
		log.info("这是一个修改商品的操作");
		int num=0;
		String sql="update shop_goods set goods_name =?,goods_price =?,goodstype_id =?,goods_img = ?,goods_desc = ?,goods_exchange = ?,goods_integral =?," + 
				"goods_detail = ?," + 
				"goods_introduction = ?," + 
				"goods_brand = ?" + 
				" where goods_id = ?";
		log.debug("传入值:"+t.toString());
		log.debug("SQL:"+sql);
		try {
			num=qr.execute(sql, t.getGoods_name(),t.getGoods_price(),t.getGoodstype_id(),t.getGoods_img(),t.getGoods_desc(),t.getGoods_exchange(),t.getGoods_integral(),t.getGoods_detail(),t.getGoods_introduction(),t.getGoods_brand(),t.getGoods_id());
		} catch (SQLException e) {
			log.error("这是一个修改商品的错误");
		}
		return num;
	}

	@Override
	public List<ShopGoods> findAll() {
		log.info("这是一个查询所有商品的操作");
		List<ShopGoods> list = null;
		String sql="select * from shop_goods";
		log.debug("SQL:"+sql);
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopGoods.class));
		} catch (SQLException e) {
			log.error("这是一个查询所有商品的错误");
		}
		return list;
	}

	@Override
	public ShopGoods findById(int id) {
		log.info("这是一个通过id查询所有商品的操作");
		ShopGoods goods=null;
		String sql="select * from shop_goods where goods_id= ?";
		log.debug("传入值:"+id);
		log.debug("SQL:"+sql);
		try {
			goods=qr.query(sql, new BeanHandler<>(ShopGoods.class),id);
		} catch (SQLException e) {
			log.error("这是一个通过id查询所有商品的错误");
		}
		return goods;
	}

	@Override
	public List<ShopGoods> LikeColumnByValue(String columName, Object value) {

		return null;
	}

	@Override
	public List<ShopGoods> getGoodsLikeName(String name) {
		log.info("这是一个通过名字模糊查询商品的操作");
		List<ShopGoods> list = null;
		String sql="select * from shop_goods where goods_name like ?";
		log.debug("传入值:"+name);
		log.debug("SQL:"+sql);
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopGoods.class),"%"+name+"%");
		} catch (SQLException e) {
			log.error("这是一个通过名字模糊查询商品的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsByPrice(double min, double max,int id) {
		log.info("这是一个通过类型和价格区间查询商品的操作");
		List<ShopGoods> list = null;
		String sql="select * from shop_goods where goods_price >? and goods_price <? and goodstype_id=?";
		log.debug("传入值:"+"min:"+min+"max:"+max+"id:"+id);
		log.debug("SQL:"+sql);
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopGoods.class),min,max,id);
		} catch (SQLException e) {
			log.error("这是一个通过类型和价格区间查询商品的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsByType(int goodstypeid) {
		log.info("这是一个通过类型查询商品的操作");
		List<ShopGoods> list = null;
		String sql="select * from shop_goods where goodstype_id = ?";
		log.debug("传入值:"+goodstypeid);
		log.debug("SQL:"+sql);
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopGoods.class),goodstypeid);
		} catch (SQLException e) {
			log.error("这是一个通过类型查询商品的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsByBrand(String brand) {
		log.info("这是一个通过品牌查询商品的操作");
		List<ShopGoods> list = null;
		String sql="select * from shop_goods where goods_brand = ?";
		log.debug("传入值:"+brand);
		log.debug("SQL:"+sql);
		try {
			list=qr.query(sql, new BeanListHandler<>(ShopGoods.class),brand);
		} catch (SQLException e) {
			log.error("这是一个通过品牌查询商品的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findPage(int pageIndex, int pageSize) {
		log.info("这是一个商品分页的操作");
		int max = pageIndex*pageSize;
		int min = (pageIndex-1)*pageSize;
		String sql = "select *from "
				+ "(select rownum r,d.* from "
				+ "(select * from shop_goods order by goods_id) d "
				+ "where rownum<=?)"
				+ " where r>?";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"pageIndex:"+pageIndex+"pageSize"+pageSize);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),max,min);
		} catch (SQLException e) {
			log.error("这是一个商品分页的错误");
		}
		return list;
	}

	@Override
	public int getCount() {
		log.info("这是一个商品总行数的操作");
		int num=0;
		String sql="select count(1) from shop_goods";
		log.debug("SQL:"+sql);
		try {
			num=qr.query(sql, new ScalarHandler<BigDecimal>()).intValue();
		} catch (SQLException e) {
			log.error("这是一个商品总行数的错误");
		}
		return num;
	}

	@Override
	public List<ShopGoods> findGoodsPageByName(String name, int pageIndex, int pageSize) {
		log.info("这是一个按商品名称分页的操作");
		int max = pageIndex*pageSize;
		int min = (pageIndex-1)*pageSize;
		String sql = "select *from "
				+ "(select rownum r,d.* from "
				+ "(select * from shop_goods where goods_name = ? order by goods_id) d "
				+ "where rownum<=?)"
				+ " where r>?";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"name:"+name+"pageIndex:"+pageIndex+"pageSize"+pageSize);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),name,max,min);
		} catch (SQLException e) {
			log.error("这是一个按商品名称分页的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsPageByPrice(double min, double max, int pageIndex, int pageSize) {
		log.info("这是一个按商品价格分页的操作");
		int pmax = pageIndex*pageSize;
		int pmin = (pageIndex-1)*pageSize;
		String sql = "select *from "
				+ "(select rownum r,d.* from "
				+ "(select * from shop_goods where goods_price > ? and goods_price < ? order by goods_id) d "
				+ "where rownum<=?)"
				+ " where r>?";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"min:"+min+"max:"+max+"pageIndex:"+pageIndex+"pageSize"+pageSize);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),min,max,pmax,pmin);
		} catch (SQLException e) {
			log.error("这是一个按商品价格分页的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsPageByType(int goodstypeid, int pageIndex, int pageSize) {
		log.info("这是一个按商品类型分页的操作");
		int max = pageIndex*pageSize;
		int min = (pageIndex-1)*pageSize;
		String sql = "select *from "
				+ "(select rownum r,d.* from "
				+ "(select * from shop_goods where goodstype_id = ? order by goods_id) d "
				+ "where rownum<=?)"
				+ " where r>?";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"goodstypeid:"+goodstypeid+"pageIndex:"+pageIndex+"pageSize"+pageSize);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),goodstypeid,max,min);
		} catch (SQLException e) {
			log.error("这是一个按商品类型分页的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsPageByBrand(String brand, int pageIndex, int pageSize) {
		log.info("这是一个按商品品牌分页的操作");
		int max = pageIndex*pageSize;
		int min = (pageIndex-1)*pageSize;
		String sql = "select *from "
				+ "(select rownum r,d.* from "
				+ "(select * from shop_goods where goods_brand = ? order by goods_id) d "
				+ "where rownum<=?)"
				+ " where r>?";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"brand:"+brand+"pageIndex:"+pageIndex+"pageSize"+pageSize);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),brand,max,min);
		} catch (SQLException e) {
			log.error("这是一个按商品品牌分页的错误");
		}
		return list;
	}

	@Override
	public int getCountByName(String name) {
		log.info("这是一个商品名称分页总行数的操作");
		int num=0;
		String sql="select count(1) from shop_goods where goods_name = ?";
		log.debug("SQL:"+sql);
		try {
			num=qr.query(sql, new ScalarHandler<BigDecimal>(),name).intValue();
		} catch (SQLException e) {
			log.error("这是一个商品名称分页总行数的错误");
		}
		return num;
	}

	@Override
	public int getCountByPrice(double min, double max) {
		log.info("这是一个商品价格分页总行数的操作");
		int num=0;
		String sql="select count(1) from shop_goods where goods_price > ? and goods_price < ?";
		log.debug("SQL:"+sql);
		try {
			num=qr.query(sql, new ScalarHandler<BigDecimal>(),min,max).intValue();
		} catch (SQLException e) {
			log.error("这是一个商品价格分页总行数的错误");
		}
		return num;
	}

	@Override
	public int getCountByType(int typeid) {
		log.info("这是一个商品类型分页总行数的操作");
		int num=0;
		String sql="select count(1) from shop_goods where goodstype_id = ?";
		log.debug("SQL:"+sql);
		try {
			num=qr.query(sql, new ScalarHandler<BigDecimal>(),typeid).intValue();
		} catch (SQLException e) {
			log.error("这是一个商品类型分页总行数的错误");
		}
		return num;
	}

	@Override
	public int getCountByBrand(String brand) {
		log.info("这是一个商品品牌分页总行数的操作");
		int num=0;
		String sql="select count(1) from shop_goods where goods_brand = ?";
		log.debug("SQL:"+sql);
		try {
			num=qr.query(sql, new ScalarHandler<BigDecimal>(),brand).intValue();
		} catch (SQLException e) {
			log.error("这是一个商品品牌分页总行数的错误");
		}
		return num;
	}

	@Override
	public List<ShopGoods> findGoodsPageByName(String name) {
		log.info("这是一个按商品名称分页的操作");
		String sql = "select * from shop_goods where goods_name = ? order by goods_id";
		List<ShopGoods> list = null;
		log.debug("传入值:"+"name:"+name);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),name);
		} catch (SQLException e) {
			log.error("这是一个按商品名称分页的错误");
		}
		
		return list;
	
	}

	@Override
	public List<ShopGoods> findGoodsPageByPrice(double min, double max) {
		log.info("这是一个按商品价格分页的操作");
		String sql = "select * from shop_goods where goods_price > ? and goods_price < ? order by goods_id";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"min:"+min+"max:"+max);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),min,max);
		} catch (SQLException e) {
			log.error("这是一个按商品价格分页的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsPageByType(int goodstypeid) {
		log.info("这是一个按商品类型分页的操作");
		
		String sql = "select * from shop_goods where goodstype_id = ? order by goods_id";

		List<ShopGoods> list = null;
		log.debug("传入值:"+"goodstypeid:"+goodstypeid);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),goodstypeid);
		} catch (SQLException e) {
			log.error("这是一个按商品类型分页的错误");
		}
		return list;
	}

	@Override
	public List<ShopGoods> findGoodsPageByBrand(String brand) {
		log.info("这是一个按商品品牌分页的操作");
		String sql = "select * from shop_goods where goods_brand = ?";
		List<ShopGoods> list = null;
		log.debug("传入值:"+"brand:"+brand);
		log.debug("SQL:"+sql);
		try {
			list = qr.query(sql, new BeanListHandler<>(ShopGoods.class),brand);
		} catch (SQLException e) {
			log.error("这是一个按商品品牌分页的错误");
		}
		return list;
	}


}
